from functools import lru_cache
import os
from pathlib import Path
import numpy as np
import datetime
import pandas as pd
import statsmodels.api as sm
from gypb.db import get_ddbapi,get_rqapi
import matplotlib
import matplotlib.pyplot as plt
import matplotlib.ticker as ticker
import matplotlib.dates as mdates
matplotlib.use('agg')
#无风险利率
R_f = 0.025

#全年交易日数
annual_trade_days = 250

dict_index_code ={'上证指数':'000001.SSE','深证成指':'399001.SZSE','创业板指':'399006.SZSE','科创50':'000688.SSE','上证50':'000016.SSE',\
                  '沪深300':'000300.SSE','中证500':'000905.SSE','中证800':'000906.SSE','中证1000':'000852.SSE','中证全指':'000985.SSE',\
                  '中证全债':'H11001.SSE','中证国债':'H11006.CSI','中证转债':'000832.SSE','商品CFCI':'H11061.SSE','万德全A':'000985.SSE',\
                  '南华商品':'H11061.SSE',"中证2000":"932000.OF","国证2000":"399303.SZSE","中证A500":"000510.SSE"}

@lru_cache
def get_trade_calendar(api):
    tradedaydf = api.get_tradedays()
    tradedays = tradedaydf[tradedaydf["SSE"] == 1].index
    return tradedays.to_frame(index=False, name='trade_date')


rqapi = get_rqapi()

TRADE_CALENDAR = get_trade_calendar(rqapi)

#定义相关功能函数
#************************************************************************************************************************
def get_trade_dates(start_date=None, end_date=None, count=None):
    '''
    获取交易日期，如无参数输入，则返回全部交易日期，返回值为datetime的list
    start_date: 开始时间，datetime格式   
    end_date: 结束时间，datetime格式
    count: 返回数据条数, 指定start_date及count时返回start_date向后count条数据, 指定end_date及count时返回end_date向前count条数据
    '''

    #根据输入参数start_date ,end_date ,count的值确定输出数据
    if start_date and end_date and count:        
        raise ValueError ('start_date ,end_date ,count 必须三选二')
        
    elif start_date and count :        
        return TRADE_CALENDAR[TRADE_CALENDAR['trade_date']>=start_date]['trade_date'].head(count).tolist()
    
    elif end_date and count :        
        return TRADE_CALENDAR[TRADE_CALENDAR['trade_date']<=end_date]['trade_date'].tail(count).tolist()
    
    elif start_date and end_date:        
        return TRADE_CALENDAR[(TRADE_CALENDAR['trade_date']<=end_date)&\
                              (TRADE_CALENDAR['trade_date']>=start_date)]['trade_date'].tolist()           
    
    elif not start_date and not end_date and not  count:        
        return TRADE_CALENDAR['trade_date'].tolist()   
    
    else:        
        raise ValueError ('start_date ,end_date ,count 必须三选二')
        

def get_trade_date_bar(freq='W', n=-1, start_date=None, end_date=None, count=None):
    """
    获取每年、每月或每周第n个交易日的历史数据
    freq: 频率, "Y"代表年, "W"代表周 或者 "M"代表月
    n: 第几个交易日, 每月/周第一个交易日:n=1,每月/周最后一个交易日:n= -1,如果不足abs(n)个交易日,则返回符合条件的最近的一个交易日 
    start_date: 开始时间,datetime格式   
    end_date: 结束时间，datetime格式
    count: 返回数据条数, 指定start_date及count时返回start_date向后count条数据, 指定end_date及count时返回end_date向前count条数据
    返回: datetime的list
    """
    #获取全部交易日期
    ca = TRADE_CALENDAR.copy()    
    
    #计算该交易日的年份
    ca['year'] = ca['trade_date'].apply(lambda x : x.year)    
    
    #计算该交易日的月份
    ca['month'] = ca['trade_date'].apply(lambda x : x.month)  
    
    #计算该交易日是该年的第几周
    ca['week'] = ca['trade_date'].apply(lambda x : x.week)   
    
    #判断是要输出每周还是每月的第n个交易日，确定分组依据
    if freq == 'Y':        
        group_list = ['year']  
        
    elif freq == 'M': 
        group_list = ['year','month'] 
        
    elif freq == 'W':        
        group_list = ['year','week']    
        
    else :
        raise ValueError ('只支持M参数为:"Y","M"或"W" ')        
    
    #判断输入参数n的类型是否为整数值，并根据n的正负进行处理
    if not isinstance(n , int):        
        raise ValueError ('n 参数应该是一个int')      
        
    elif n >0 :        
        #n为正值，先分组取出前n个交易日数据，再取每组最后一个交易日，即为第n个交易日
        bars = ca.groupby(group_list,as_index=False).head(n).groupby(group_list,as_index=False).last()  
        
    elif n< 0:        
        #n为负值，先分组取出后（-n）个交易日数据，再取每组第一个交易日，即为倒数第n个交易日
        bars = ca.groupby(group_list,as_index=False).tail(-n).groupby(group_list,as_index=False).first()     
        
    else :        
        raise ValueError ('n 参数错误 : n={}'.format(n))        
        
    #根据输入参数start_date ,end_date ,count的值确定输出数据
    if start_date and end_date and count:        
        raise ValueError ('start_date ,end_date ,count 必须三选二')
        
    elif start_date and count :        
        return bars[bars['trade_date']>=start_date].head(count)['trade_date'].tolist()    
    
    elif end_date and count :        
        return bars[bars['trade_date']<=end_date].tail(count)['trade_date'].tolist()  
    
    elif start_date and end_date:        
        return bars[(bars['trade_date']<=end_date)&(bars['trade_date']>=start_date)]['trade_date'].tolist()    
    
    elif not start_date and not end_date and not  count:        
        return bars['trade_date'].tolist()    
    
    else:        
        raise ValueError ('start_date ,end_date ,count 必须三选二')
    
#绝对收益指标评分，输入产品的策略类型和对应的风险指标值
def absolute_return_scoring(pro_strategy_type,abs_return):
   
    #策略类型为市场中性、套利策略及其他低波策略产品
    if pro_strategy_type in ['市场中性','套利策略','其他低波策略']:
        if abs_return<0:
            score = 0
        elif abs_return<0.03:
            score = 15
        elif abs_return<0.05:
            score = 30
        elif abs_return<0.07:
            score = 40
        else:
            score = 50
    
    else:
        score = 0
    
    return score

#相对收益指标评分
def excess_return_scoring(pro_strategy_type,exc_return):
    
    #策略类型为股票多头、股票多空
    if pro_strategy_type in ['股票多头','股票多空']:
        if exc_return<0:
            score = 0
        elif exc_return<0.05:
            score = 15
        elif exc_return<0.1:
            score = 30
        elif exc_return<0.12:
            score = 40
        else:
            score = 50
            
    #策略类型为固收类策略 （已更新）   
    elif pro_strategy_type in ['债券策略','其他固收类策略']:
        if exc_return<0:
            score = 0
        elif exc_return<0.01:
            score = 15
        elif exc_return<0.02:
            score = 30
        else:
            score = 50
    #策略类型为CTA策略、复合策略及其他产品（已更新）
    elif pro_strategy_type in ['CTA策略','复合策略','其他']:
        if exc_return<0:
            score = 0
        elif exc_return<0.05:
            score = 15
        elif exc_return<0.08:
            score = 30
        elif exc_return<0.10:
            score = 40
        else:
            score = 50
    
    else:
        score = 0
    
    return score

#最大回撤评分
def maxdown_scoring(pro_strategy_type,maxdown):

    #策略类型为市场中性、套利策略及其他低波策略产品
    if pro_strategy_type in ['市场中性','套利策略','其他低波策略']:
        if maxdown>0.06:
            score = 0
        elif maxdown>0.04:
            score = 8
        elif maxdown>0.02:
            score = 12
        elif maxdown>0.01:
            score = 16
        else:
            score = 20
    else:
        score = 0

    return score


#相对回撤指标评分
def excess_maxdown_scoring(pro_strategy_type,exc_maxdown):

    #策略类型为股票多头、股票多空
    if pro_strategy_type in ['股票多头','股票多空']:
        if exc_maxdown>0.12:
            score = 0
        elif exc_maxdown>0.08:
            score = 4
        elif exc_maxdown>0.05:
            score = 8
        elif exc_maxdown>0:
            score = 12
        elif exc_maxdown>-0.05:
            score = 16
        else:
            score = 20
            
   #策略类型为固收类策略
    elif pro_strategy_type in ['债券策略','其他固收类策略']:
        if exc_maxdown>0.01:
            score = 0
        elif exc_maxdown>0:
            score = 10
        elif exc_maxdown>-0.01:
            score = 15
        else:
            score = 20
            
    #策略类型为CTA策略、复合策略、其他策略    
    elif pro_strategy_type in ['CTA策略','复合策略','其他']:
        if exc_maxdown>0.1:
            score = 0
        elif exc_maxdown>0.06:
            score = 4
        elif exc_maxdown>0.03:
            score = 8
        elif exc_maxdown>0:
            score = 12
        elif exc_maxdown>-0.03:
            score = 16
        else:
            score = 20
    
    else:
        score = 0   
        
    return score


#夏普比率评分
def sharpe_ratio_scoring(sharpe_ratio):
    
    if sharpe_ratio<0:
        score = 0
    elif sharpe_ratio<0.8:
        score = 2
    elif sharpe_ratio<1:
        score = 4
    elif sharpe_ratio<1.2:
        score = 6
    elif sharpe_ratio<1.5:
        score = 8
    else:
        score = 10
        
    return score


#索提诺比率评分
def sortino_ratio_scoring(sortino_ratio):
   
    if sortino_ratio<0:
        score = 0
    elif sortino_ratio<1:
        score = 2
    elif sortino_ratio<1.2:
        score = 4
    elif sortino_ratio<1.5:
        score = 6
    elif sortino_ratio<2:
        score = 8
    else:
        score = 10
        
    return score


#信息比率评分
def information_ratio_scoring(information_ratio):

    if information_ratio<0:
        score = 0
    elif information_ratio<1:
        score = 2
    elif information_ratio<1.2:
        score = 4
    elif information_ratio<1.5:
        score = 6
    elif information_ratio<2:
        score = 8
    else:
        score = 10
    
    return score

#产品评分，输入参数product_data表
def get_product_score(product_data):
    
    #计算产品规模权重
    product_data['scale_weight'] = product_data['scale']/product_data['scale'].sum()
    
    #初始化各产品的各指标得分为0
    product_data['absolute_return_score'] = 0
    product_data['excess_return_score'] = 0
    product_data['maxdown_score']=0
    product_data['excess_maxdown_score'] = 0
    product_data['sharpe_ratio_score'] = 0
    product_data['sortino_ratio_score'] = 0
    product_data['information_ratio_score'] = 0
    
    #计算每个产品的风险收益指标得分
    product_data['absolute_return_score'] = product_data.apply(lambda x:absolute_return_scoring(x['strategy_type'],x['pro_annual_return']),axis=1)
    product_data['excess_return_score'] = product_data.apply(lambda x:excess_return_scoring(x['strategy_type'],x['pro_excess_return']),axis=1)
    product_data['maxdown_score'] = product_data.apply(lambda x:maxdown_scoring(x['strategy_type'],x['pro_maxdown']),axis=1)
    product_data['excess_maxdown_score'] = product_data.apply(lambda x:excess_maxdown_scoring(x['strategy_type'],x['pro_excess_maxdown']),axis=1)
    product_data['sharpe_ratio_score'] = product_data.apply(lambda x:sharpe_ratio_scoring(x['pro_sharpe_ratio']),axis=1)
    product_data['sortino_ratio_score'] = product_data.apply(lambda x:sortino_ratio_scoring(x['pro_sortino_ratio']),axis=1)
    product_data['information_ratio_score'] = product_data.apply(lambda x:information_ratio_scoring(x['pro_information_ratio']),axis=1)
        
    #绝对收益指标总得分
    #absolute_return_total_score = (product_data['absolute_return_score']*product_data['scale_weight']).sum()
    absolute_return_total_score = (product_data['other_absolute_return_score']*product_data['scale_weight']).sum()
    
    #相对收益指标总得分
    #excess_return_total_score = (product_data['excess_return_score']*product_data['scale_weight']).sum()
    excess_return_total_score = (product_data['other_excess_return_score']*product_data['scale_weight']).sum()
    
    #产品收益总得分
    product_return_total_score = absolute_return_total_score+excess_return_total_score

    #最大回撤指标总得分
    maxdown_total_score = (product_data['other_maxdown_score']*product_data['scale_weight']).sum()

    #相对回撤指标总得分
    #excess_maxdown_total_score = (product_data['excess_maxdown_score']*product_data['scale_weight']).sum()
    excess_maxdown_total_score = (product_data['other_excess_maxdown_score']*product_data['scale_weight']).sum()
    
    #产品回撤总得分
    product_maxdown_total_score = maxdown_total_score + excess_maxdown_total_score


    #夏普比率指标总得分
    #sharpe_ratio_total_score = (product_data['sharpe_ratio_score']*product_data['scale_weight']).sum()
    sharpe_ratio_total_score = (product_data['other_sharpe_ratio_score']*product_data['scale_weight']).sum()
    
    #索提诺比率指标总得分
    #sortino_ratio_total_score = (product_data['sortino_ratio_score']*product_data['scale_weight']).sum()
    sortino_ratio_total_score = (product_data['other_sortino_ratio_score']*product_data['scale_weight']).sum()
    
    #信息比率指标总得分
    #information_ratio_total_score = (product_data['information_ratio_score']*product_data['scale_weight']).sum()
    information_ratio_total_score = (product_data['other_information_ratio_score']*product_data['scale_weight']).sum()
    
    #产品定量总得分
    product_total_score = absolute_return_total_score+excess_return_total_score+maxdown_total_score+excess_maxdown_total_score+\
                          sharpe_ratio_total_score+sortino_ratio_total_score+information_ratio_total_score
    #产品加权总得分
    product_w_score = product_total_score*0.4
    
    #返回各指标得分数据的字典
    product_score ={'absolute_return_score':absolute_return_total_score,'excess_return_score':excess_return_total_score,'product_return_total_score':product_return_total_score,\
               'maxdown_score':maxdown_total_score,'excess_maxdown_score':excess_maxdown_total_score,'sharpe_ratio_score':sharpe_ratio_total_score,'product_maxdown_total_score':product_maxdown_total_score,\
               'sortino_ratio_score':sortino_ratio_total_score,'information_ratio_score':information_ratio_total_score,\
               'product_total_score':product_total_score,'product_w_score':product_w_score}
    
    return product_score

'''
风险收益指标计算器，支持的风险收益指标如下：
累计收益率、年化收益率、最大回撤、夏普比率、索提诺比率、信息比率、卡玛比率，阿尔法(年化)、贝塔
'''
#定义相关功能函数
#************************************************************************************************************************
#计算期间累计收益率，传入参数为价格时间序列，日期为index
def cal_cumulative_return(price_series):
    
    #价格序列少于2个数值，无法计算累计收益率，返回np.nan值
    if len(price_series)<2:
        
        print('传入的价格序列少于2个数值，无法计算累计收益率，返回空值！')
        return np.nan
    
    else:
        #返回累计收益率
        return price_series[-1]/price_series[0]-1
    

#计算期间年化收益率，传入参数为价格时间序列，日期为index
def cal_annual_return(price_series):
    
    #价格序列少于2个数值，无法计算年化收益率，返回np.nan值
    if len(price_series)<2:
        
        print('传入的价格序列少于2个数值，无法计算年化收益率，返回空值！')
        return np.nan
    
    else:
        #计算期间交易日数
        trade_days = len(get_trade_dates(start_date=price_series.index[0],end_date=price_series.index[-1]))
        
        #返回年化收益率
        return np.power(price_series[-1]/price_series[0],annual_trade_days/trade_days)-1


#计算期间最大回撤，传入参数为价格时间序列，日期为index
def cal_maxdown(price_series):
    
    #价格序列少于3个数值，无法计算最大回撤，返回np.nan值
    if len(price_series)<3:
        
        print('传入的价格序列少于3个数值，无法计算最大回撤，返回空值！')
        return np.nan
    
    else:    
        fs_maxdown = [0]
        #计算区间最大回撤
        for i in range(1,len(price_series)):         
            temp = [0]
            #计算截至第i日的历史回撤幅度
            for j in range(i):            
                temp.append(price_series[i]/price_series[j]-1)   
            #取截至第i日的最大的回撤
            fs_maxdown.append(np.nanmin(temp))

        #返回区间最大回撤
        maxdown = -np.min(fs_maxdown)
        return maxdown

    
#计算夏普比率，R_f为无风险收益率
def cal_sharpe_ratio(price_series):
    
    #价格序列少于3个数值，无法计算夏普比率，返回np.nan值
    if len(price_series)<3:
        
        print('传入的价格序列少于3个数值，无法计算夏普比率，返回空值！')
        return np.nan
    
    else:        
        #计算净值序列间隔的交易天数
        trade_days = len(get_trade_dates(start_date=price_series.index[0],end_date=price_series.index[-1]))
        interval_days = (trade_days-1)/(len(price_series)-1)

        #计算波动率：收益率序列的标准差
        ret_vol = price_series.pct_change().dropna().std() 

        #计算年化波动率
        annual_vol = ret_vol* np.sqrt(annual_trade_days/interval_days)

        #计算年化收益率
        annual_return = cal_annual_return(price_series)

        #计算夏普比率
        sharpe_ratio = (annual_return - R_f)/annual_vol
        return sharpe_ratio

    
#计算索提诺比率，R_f为无风险收益率
def cal_sortino_ratio(price_series):
    
    #价格序列少于3个数值，无法计算索提诺比率，返回np.nan值
    if len(price_series)<3:
        
        print('传入的价格序列少于3个数值，无法计算索提诺比率，返回空值！')
        return np.nan
    
    else:        
        #计算净值序列间隔的交易天数
        trade_days = len(get_trade_dates(start_date=price_series.index[0],end_date=price_series.index[-1]))
        interval_days = (trade_days-1)/(len(price_series)-1)

        #无风险收益率R_f在对应频率下的收益率，按复利计算
        R_f_freq = np.power(1+R_f,interval_days/annual_trade_days)-1

        #超额收益率序列
        excess_ret = price_series.pct_change().dropna()-R_f_freq

        #计算下行波动率：超过无风险收益率的收益率序列的标准差
        down_ret = excess_ret[excess_ret<0]
        down_ret_vol = down_ret.std()
        #np.sqrt(np.power(excess_ret[excess_ret<0],2).sum()/(len(excess_ret)-1))

        #计算年化下行波动率
        down_annual_vol = down_ret_vol* np.sqrt(annual_trade_days/interval_days)

        #计算年化收益率
        annual_return = cal_annual_return(price_series)

        #计算索提诺比率
        sortino_ratio = (annual_return - R_f)/down_annual_vol

        return sortino_ratio
    

#计算信息比率，benchmark_series为基准指数的时间序列
def cal_information_ratio(price_series,benchmark_series):
    
    #检查序列是否对齐
    if len(price_series) == len(benchmark_series):
        
        #价格序列少于3个数值，无法计算信息比率，返回np.nan值
        if len(price_series)<3:

            print('传入的价格序列少于3个数值，无法计算信息比率，返回空值！')
            return np.nan

        else:        
        
            #计算净值序列间隔的交易天数
            trade_days = len(get_trade_dates(start_date=price_series.index[0],end_date=price_series.index[-1]))
            interval_days = (trade_days-1)/(len(price_series)-1)

            #计算年化超额收益率
            annual_excess_return = cal_annual_return(price_series) - cal_annual_return(benchmark_series)    

            #超额收益率序列
            excess_ret = price_series.pct_change().dropna()- benchmark_series.pct_change().dropna()

            #计算年化超额收益波动率
            annual_excess_vol = excess_ret.std()* np.sqrt(annual_trade_days/interval_days)

            #计算信息比例
            information_ratio = annual_excess_return/annual_excess_vol

            return information_ratio
    else:
        print(f'产品净值{len(price_series)}')
        print(f'对标基准{len(benchmark_series)}')
        raise ValueError('产品净值序列和对标基准序列不匹配！')
        
        
#计算期间卡玛比率，传入参数为价格时间序列，日期为index
def cal_calmar_ratio(price_series):
    
    #价格序列少于3个数值，无法计算卡玛比率，返回np.nan值
    if len(price_series)<3:
        
        print('传入的价格序列少于3个数值，无法计算卡玛比率，返回空值！')
        return np.nan
    
    else:    
        
        #计算区间年化收益率
        annual_return = cal_annual_return(price_series)
        
        #计算区间最大回撤
        maxdown = cal_maxdown(price_series)
        
        #返回卡玛比率
        return annual_return/maxdown


#计算年化阿尔法和贝塔，传入参数为价格时间序列，日期为index
def cal_alpha_and_beta(price_series,benchmark_series):
    
    #检查序列是否对齐
    if len(price_series) == len(benchmark_series):
        
        #价格序列少于3个数值，无法计算阿尔法和贝塔，返回np.nan值
        if len(price_series)<3:

            print('传入的价格序列少于3个数值，无法计算阿尔法和贝塔，返回空值！')
            return {'annual_alpha':np.nan,'beta':np.nan}

        else:        
            
            #收益率序列
            pro_ret = price_series.pct_change().dropna()
            bm_ret = benchmark_series.pct_change().dropna()
            
            #回归系数beta
            X = sm.add_constant(bm_ret)
            model = sm.OLS(pro_ret,X)
            ols_results = model.fit()
            beta = ols_results.params[1]
            
            #计算年化超额收益率
            pro_annual_excess_return = cal_annual_return(price_series) - R_f
            bm_annual_excess_return = cal_annual_return(benchmark_series) - R_f 
            
            #计算年化alpha
            annual_alpha = pro_annual_excess_return - beta*bm_annual_excess_return    

            return {'annual_alpha':annual_alpha,'beta':beta}
    else:
        
        raise ValueError('产品净值序列和对标基准序列不匹配！')
    

#计算单个产品的风险收益指标，返回值为字典:
def get_risk_return(pro_strategy_type,pro_series,bm_series):

    #产品净值起始日期
    start_date = pro_series.index[0]
        
    #产品净值结束日期
    end_date = pro_series.index[-1]
    
    
    #该产品及对标基准成立以来累计收益、年化收益、最大回撤、詹森alpha、夏普比率、卡玛比率、索提诺比率、信息比率
    total_cumulative_return = cal_cumulative_return(pro_series)
    bm_total_cumulative_return = cal_cumulative_return(bm_series)
    
    total_annual_return = cal_annual_return(pro_series)
    bm_total_annual_return = cal_annual_return(bm_series)
    
    total_maxdown = cal_maxdown(pro_series)
    bm_total_maxdown = cal_maxdown(bm_series)
    
    #total_annual_alpha_beta = cal_alpha_and_beta(pro_series,bm_series)
    #total_annual_alpha = total_annual_alpha_beta['annual_alpha']
    
    total_information_ratio = cal_information_ratio(pro_series,bm_series)
    
    total_sharpe_ratio = cal_sharpe_ratio(pro_series)
    bm_total_sharpe_ratio = cal_sharpe_ratio(bm_series)
    
    total_calmar_ratio = cal_calmar_ratio(pro_series)
    bm_total_calmar_ratio = cal_calmar_ratio(bm_series)
    
    total_sortino_ratio = cal_sortino_ratio(pro_series)
    bm_total_sortino_ratio = cal_sortino_ratio(bm_series)
    
    
    #该产品今年以来收益、最大回撤
    year_dates = get_trade_date_bar(freq='Y',n=-1,end_date=end_date,count=3)
    this_year_begin_date = [dt for dt in year_dates if dt < end_date][-1]
    print(this_year_begin_date)

    this_year_pro_series = pro_series[(pro_series.index>=this_year_begin_date)&(pro_series.index<=end_date)]  
    this_year_bm_series = bm_series[(bm_series.index>=this_year_begin_date)&(bm_series.index<=end_date)]  

    this_year_cumulative_return = cal_cumulative_return(this_year_pro_series)
    bm_this_year_cumulative_return = cal_cumulative_return(this_year_bm_series)

    this_year_maxdown = cal_maxdown(this_year_pro_series)
    bm_this_year_maxdown = cal_maxdown(this_year_bm_series)
    
    #该产品今年以来收益、最大回撤
    year_dates = get_trade_date_bar(freq='Y',n=-1,end_date=end_date,count=3)
    this_year_begin_date = [dt for dt in year_dates if dt < end_date][-1]
    print(this_year_begin_date)
    
    this_year_pro_series = pro_series[(pro_series.index>=this_year_begin_date)&(pro_series.index<=end_date)]  
    this_year_bm_series = bm_series[(bm_series.index>=this_year_begin_date)&(bm_series.index<=end_date)]  
    
    this_year_cumulative_return = cal_cumulative_return(this_year_pro_series)
    bm_this_year_cumulative_return = cal_cumulative_return(this_year_bm_series)
    
    this_year_maxdown = cal_maxdown(this_year_pro_series)
    bm_this_year_maxdown = cal_maxdown(this_year_bm_series)

    #季度移动平均：统计各风险指标    
    #滚动统计周期的起止日期，滚动窗口1年，起始点3个月移动
    stat_end_date = end_date
    stat_begin_date = stat_end_date - datetime.timedelta(days=365)
    
    #记录各统计周期下风险收益指标
    s_annual_return = []
    s_excess_return = []
    #增加最大回撤
    s_maxdown = []
    s_excess_maxdown = []
    s_sharpe_ratio = []
    s_sortino_ratio = []
    s_information_ratio = []
    
    #记录各统计周期下风险收益指标得分
    s_absolute_return_score = []
    s_excess_return_score = []
    s_maxdown_score = []
    s_excess_maxdown_score = []
    s_sharpe_ratio_score = []
    s_sortino_ratio_score = []
    s_information_ratio_score = []    
    
    
    #滚动周期的起始日期stat_begin_date超出产品净值起始日期180天，则滚动循环结束
    i = 0
    
    #用于标志循环是否结束
    last_loop = False
    
    while stat_begin_date > start_date - datetime.timedelta(days=180) and (not last_loop):
        
        #若该滚动周期的起始日期stat_begin_date超出产品净值截止日期start_date(即该统计周期不足一年)
        if stat_begin_date < start_date:
            
            #则取从start_date至stat_end_date为统计周期
            stat_begin_date = start_date
            
            #标志本次循环为最后一次
            last_loop = True
            
        # print(f'执行循环{i+1}：该统计周期为：{stat_begin_date.date()} 至 {stat_end_date.date()}\n**************************\n')
        
        #取从统计时点开始的产品净值序列及其对应的基准序列
        period_pro_series = pro_series[(pro_series.index>=stat_begin_date)&(pro_series.index<=stat_end_date)]
        period_bm_series = bm_series.loc[period_pro_series.index.tolist()]

        #计算收益风险指标   
        #累计收益
        #pro_cumulative_return = cal_cumulative_return(period_pro_series)
        #bm_cumulative_return = cal_cumulative_return(period_bm_series)

        #年化收益
        pro_annual_return = cal_annual_return(period_pro_series)
        bm_annual_return = cal_annual_return(period_bm_series)

        #年化超额收益
        pro_excess_return = pro_annual_return - bm_annual_return

        #最大回撤
        pro_maxdown = cal_maxdown(period_pro_series)
        bm_maxdown = cal_maxdown(period_bm_series)
        
        #相对最大回撤
        pro_excess_maxdown = pro_maxdown-bm_maxdown
     
        #夏普比率
        pro_sharpe_ratio = cal_sharpe_ratio(period_pro_series)
        #bm_sharpe_ratio = cal_sharpe_ratio(period_bm_series)

        #索提诺比率
        pro_sortino_ratio = cal_sortino_ratio(period_pro_series)
        #bm_sortino_ratio = cal_sortino_ratio(period_bm_series)

        #信息比率
        pro_information_ratio = cal_information_ratio(period_pro_series,period_bm_series)
        
        #记录该统计周期下的风险收益指标值
        s_annual_return.append(pro_annual_return)
        s_absolute_return_score.append(absolute_return_scoring(pro_strategy_type,pro_annual_return)) 
        # print(f'区间年化收益为：{pro_annual_return:.2%}，得分为：{absolute_return_scoring(pro_strategy_type,pro_annual_return)}\n')
        
        s_excess_return.append(pro_excess_return)
        s_excess_return_score.append(excess_return_scoring(pro_strategy_type,pro_excess_return)) 
        # print(f'区间相对收益为：{pro_excess_return:.2%}，得分为：{excess_return_scoring(pro_strategy_type,pro_excess_return)}\n')
        
        #增加最大回撤
        s_maxdown.append(pro_maxdown)
        s_maxdown_score.append(maxdown_scoring(pro_strategy_type,pro_maxdown))
        #print(f'区间最大回撤为：{pro_maxdown:.2%}，得分为：{maxdown_scoring(pro_strategy_type,pro_maxdown)}\n')

        s_excess_maxdown.append(pro_excess_maxdown)
        s_excess_maxdown_score.append(excess_maxdown_scoring(pro_strategy_type,pro_excess_maxdown)) 
        # print(f'区间相对回撤为：{pro_excess_maxdown:.2%}，得分为：{excess_maxdown_scoring(pro_strategy_type,pro_excess_maxdown)}\n')
        
        s_sharpe_ratio.append(pro_sharpe_ratio)
        s_sharpe_ratio_score.append(sharpe_ratio_scoring(pro_sharpe_ratio)) 
        # print(f'区间夏普比率为：{pro_sharpe_ratio:.2f}，得分为：{sharpe_ratio_scoring(pro_sharpe_ratio)}\n')
        
        s_sortino_ratio.append(pro_sortino_ratio)
        s_sortino_ratio_score.append(sortino_ratio_scoring(pro_sortino_ratio)) 
        # print(f'区间索提诺比率为：{pro_sortino_ratio:.2f}，得分为：{sortino_ratio_scoring(pro_sortino_ratio)}\n')
        
        s_information_ratio.append(pro_information_ratio)
        s_information_ratio_score.append(information_ratio_scoring(pro_information_ratio)) 
        # print(f'区间信息比率为：{pro_information_ratio:.2f}，得分为：{information_ratio_scoring(pro_information_ratio)}\n')

                    
        #更新统计周期的起止时间     
        stat_end_date = stat_end_date - datetime.timedelta(days=90)
        stat_begin_date = stat_end_date - datetime.timedelta(days=365) 
               
        #循环次数+1        
        i = i+1    
    
    #保存到字典
    pro_risk_return = {'pro_annual_return':np.nanmean(s_annual_return),'pro_excess_return':np.nanmean(s_excess_return),\
                 'pro_maxdown':np.nanmean(s_maxdown),\
                 'pro_excess_maxdown':np.nanmean(s_excess_maxdown),'pro_sharpe_ratio':np.nanmean(s_sharpe_ratio),\
                 'pro_sortino_ratio':np.nanmean(s_sortino_ratio),'pro_information_ratio':np.nanmean(s_information_ratio),\
                 'total_cumulative_return':total_cumulative_return,'bm_total_cumulative_return':bm_total_cumulative_return,\
                 'total_annual_return':total_annual_return,'bm_total_annual_return':bm_total_annual_return,\
                 'total_maxdown':total_maxdown,'bm_total_maxdown':bm_total_maxdown,\
                 'this_year_cumulative_return':this_year_cumulative_return,'this_year_maxdown':this_year_maxdown,\
                       'bm_this_year_cumulative_return':bm_this_year_cumulative_return,'bm_this_year_maxdown':bm_this_year_maxdown,\
                       #'total_annual_alpha':total_annual_alpha,
                       'total_information_ratio':total_information_ratio,\
                 'total_sharpe_ratio':total_sharpe_ratio, 'bm_total_sharpe_ratio':bm_total_sharpe_ratio,\
                 'total_calmar_ratio':total_calmar_ratio,'bm_total_calmar_ratio':bm_total_calmar_ratio,\
                 'total_sortino_ratio':total_sortino_ratio,'bm_total_sortino_ratio':bm_total_sortino_ratio,\
                 'other_absolute_return_score':np.nanmean(s_absolute_return_score),\
                 'other_excess_return_score':np.nanmean(s_excess_return_score),\
                 'other_maxdown_score':np.nanmean(s_maxdown_score),\
                 'other_excess_maxdown_score':np.nanmean(s_excess_maxdown_score),\
                 'other_sharpe_ratio_score':np.nanmean(s_sharpe_ratio_score),\
                 'other_sortino_ratio_score':np.nanmean(s_sortino_ratio_score),\
                 'other_information_ratio_score':np.nanmean(s_information_ratio_score)}
    
    return pro_risk_return

def get_reversion_nv_series(excel_file,pro_name):
    '''
    从excel文件(excel_file)中读取产品名称(product_name)对应的净值数据表，获取净值数据并进行预处理:
    1、删除空白行、重复行以及非交易日数据的修正处理
    2、计算复权累计净值
    返回复权累计净值序列pd.Series
    '''
    #获取产品净值数据，将第一列净值日期解析为datetime格式
    nv_data = pd.read_excel(excel_file,sheet_name=pro_name,parse_dates=[0])
    
    #删除空白行
    nv_data.dropna(inplace=True)

    #删除重复项
    nv_data.drop_duplicates(subset=['净值日期'],keep='last',inplace=True)
    
    #设置'净值日期'为索引，并按索引排序
    nv_data.set_index('净值日期',inplace=True)
    nv_data.sort_index(inplace=True)
        
    #净值起始日期
    start_date = nv_data.index[0]
        
    #净值结束日期
    end_date = nv_data.index[-1]
    
    #获取s_date至e_date之间的每个交易日
    trade_days = get_trade_dates(start_date=start_date, end_date=end_date)
    
    #产品净值数据中如有某些日期为非交易日，则需进行处理（修改为前一个交易日期或删掉）
    for dt in nv_data.index.tolist():

        #净值数据表中某一日期为非交易日
        if dt not in trade_days:

            #该日期往前的一个交易日
            trade_date_before = get_trade_dates(end_date=dt,count=3)[-1]

            #如果期其前一个交易日亦不在数据表中，则进行修改
            if trade_date_before not in nv_data.index.tolist():

                nv_data.rename(index={dt:trade_date_before},inplace = True) 

            else:#否则删除该条无效数据
                nv_data.drop(index=dt,inplace = True)

    
    #获取s_date至e_date之间的每周最后一个交易日列表，输入参数start_date,end_date为datetime格式
    week_dates = get_trade_date_bar(start_date=start_date, end_date=end_date)
                
    #判断产品净值数据周频上是否有缺失
    miss = []
    for dt in week_dates:
        #检查每周最后一个交易日是否在净值序列的index中
        if dt not in nv_data.index:
            #如不在，则说明该净值序列缺少该周频交易日的数据，记录该日期
            miss.append(dt)
            
    #日期格式转化为字符串格式
    miss = [d.strftime('%Y-%m-%d') for d in miss]
    
    if len(miss)>0:
        print(f'\n{pro_name}产品的周频净值数据缺失，缺失日期为：{miss}')      
    else:
        print(f'\n{pro_name}产品的周频净值数据完整')
        
    #计算复权累计净值
    nv_data['单期收益率'] = (nv_data['累计净值'] - nv_data.shift(1)['累计净值'])/nv_data.shift(1)['单位净值']
    nv_data['单期收益率'].fillna(0,inplace=True)
    nv_data['复权累计净值']=nv_data['累计净值'][0]*(1+nv_data['单期收益率']).cumprod()

    #返回复权累计净值序列作为风险收益指标计算依据
    return nv_data['复权累计净值']/nv_data['复权累计净值'][0]

#获取基础指数价格序列
def get_basic_index_price(index,start_date,end_date):
    '''
    获取从start_date到end_date之间指数的收盘价数据，返回值为pd.Series
    参数index为字符串：指数名称
    参数start_date、end_date为datetime
    '''
    #获取start_date到end_date之间的交易日期
    index_code = dict_index_code.get(index)
    # trade_dates = get_trade_dates(start_date=start_date,end_date=end_date)
    
    #获取该区间的指数行情序列
    return rqapi.get_factor("close",index_code,start_date,end_date).reset_index(level="code",drop=True)["close"]
    # index_series = INDEX_DATA[(INDEX_DATA.index>=start_date)&(INDEX_DATA.index<=end_date)][index]
    
    # 返回该区间所有交易日的指数行情
    # return index_series.loc[trade_dates]

#获取对标基准（单个指数，或多个指数的加权混合）净值序列
def get_benchmark_series(benchmark,start_date,end_date):
    '''
    获取从start_date到end_date之间对标基准的归一化净值序列数据，返回值为pd.Series
    benchmark为string类型：单个指数名称；或dict类型：基准字典，{key为指数名称: value为对应权重}
    参数start_date、end_date为datetime日期格式
    '''
    #如果传入的benchmark为字符串（即单一指数），转化为字典（权重为1），进行统一
    if isinstance(benchmark,str):
        benchmark = {benchmark:1}
    
    #先取start_date至end_date之间的全部交易日,list对象
    trade_days = get_trade_dates(start_date=start_date,end_date=end_date)
    
    #根据交易日期生成一个零值的pd.Series
    bm_series = pd.Series(0,index=trade_days)
    
    #对benchmark字典进行循环，加权计算得到对标基准的净值序列
    for index,weight in benchmark.items():
        
        #如果权重值大于0
        if weight>0:
            
            #获取成分基础指数的收盘价序列
            component_index_price = get_basic_index_price(index,start_date,end_date)
               
            #归一化净值处理后，乘以权重值
            component_series = weight*(component_index_price/component_index_price[0])
            
            #将成分基础指数净值序列加权求和
            bm_series = bm_series + component_series
            
    return bm_series

#从产品数据表excel_file文件中读取产品基本信息和净值数据，并计算风险收益指标，返回字典（统计日期、自定义基准、产品数据和风险收益指标数据）
def get_product_data(excel_file,image_dir=None,manager_name=None):
    image_dir = Path(image_dir).joinpath(f"temp_image_{manager_name}")
    if not os.path.exists(image_dir):
        os.makedirs(image_dir)
    
    #读取产品数据表中的sheet_1，不将第一行作为表头，第5列成立日期解析为datetime格式
    data = pd.read_excel(excel_file,sheet_name=0,header=None,parse_dates=[4])

    #获取产品数据统计日期，第1行第8列单元格
    stat_date = pd.to_datetime(data.iloc[0,7])

    #截取产品信息数据
    product_info_cut = data.iloc[2:,1:8].dropna()

    #生成DataFrame：product_info，保存产品基本信息
    product_info_columns = ['code','name','strategy_type','establish_date','invest_manager','benchmark','scale']
    product_info = pd.DataFrame(product_info_cut.values,columns=product_info_columns)

    #将产品代码设置位索引
    product_info.set_index('code',inplace=True)

    #获取自定义基准信息表：custom_benchmark
    custom_benchmark_info = data.iloc[17:27,10:]
    custom_benchmark_columns = ['custom_benchmark']+data.iloc[16,11:].tolist()
    custom_benchmark = pd.DataFrame(custom_benchmark_info.values,columns=custom_benchmark_columns)
    custom_benchmark.set_index('custom_benchmark',inplace=True)
    custom_benchmark.fillna(0,inplace=True)

    #初始化字典:risk_return_data，用于保存各产品的风险收益指标数据，字典的键为产品代码pro_code，值为各统计周期下的指标数据list
    risk_return_data = {}
    
    #保存净值走势图path的字典
    images = {}

    #读取每个产品净值数据表，并进行收益风险指标的计算
    for pro_code in product_info.index.tolist():
                
        #获取产品简称
        pro_name = product_info['name'][pro_code]
        
        #获取产品策略类型
        pro_strategy_type = product_info['strategy_type'][pro_code]
        
        #获取复权累计净值序列
        pro_series = get_reversion_nv_series(excel_file,pro_name)
        
        #获取该产品的对标基准
        benchmark_name = product_info['benchmark'][pro_code]
        
        #如果为自定义基准，从custom_benchmark中查询对应的成分指数和权重，转化为字典格式
        if '自定义' in benchmark_name:
            bm = {}
            for col in custom_benchmark.columns.tolist():
                bm.update({col:custom_benchmark.loc[benchmark_name,col]})    
            benchmark = bm.copy()
        else:
            benchmark = benchmark_name
            
        print(f'\n{pro_name}产品的对标基准为：{benchmark}')
        
        
        #产品净值起始日期
        start_date = pro_series.index[0]
        
        #产品净值结束日期
        end_date = pro_series.index[-1]
        
        #取该产品对标基准对应的净值序列
        bm_series = get_benchmark_series(benchmark,start_date,end_date).loc[pro_series.index.tolist()]
        
        #将产品净值和对标基准净值序列合并至一个dataframe
        pro_bm_series = pd.DataFrame(columns = [pro_name,benchmark_name])
        pro_bm_series[pro_name] = pro_series
        pro_bm_series[benchmark_name] = bm_series

        #绘制净值走势图对标基准
        ##import seaborn as sns
        ##sns.set()
        ##plt.style.use('seaborn')
        plt.rcParams['font.sans-serif'] = ['SimHei']
        plt.rcParams['axes.unicode_minus'] = False

        pro_bm_series.plot(figsize=(8,8),xlabel='',fontsize=16,linewidth=3.0,color=['sandybrown','firebrick','slategrey'])
        plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m-%d'))
        plt.gca().yaxis.set_major_formatter(ticker.FormatStrFormatter('%0.2f'))
        plt.grid(axis='x')
        plt.margins(x=0.015)
        plt.legend(loc='best',fontsize=16)#loc='upper left',
        plt.title(pro_name+'净值走势图\n',fontsize=20)

        image_path = os.path.join(image_dir,pro_name+'净值走势图.png')
        plt.savefig(image_path,bbox_inches='tight') #

        #存储图片文件到字典
        images.update({pro_code:image_path})

        # plt.show()

        
        #计算风险收益指标，并保存到字典risk_return_data
        # print(f'\n开始计算{pro_name}产品的风险收益指标:\n******************************************************************************************')
        
        risk_return_data.update({pro_code:get_risk_return(pro_strategy_type,pro_series,bm_series)})
        
    #将风险收益指标数据字典转化为dataframe，并合并到产品基本信息表product_info，生成完整的产品数据表product_data      
    product_data = pd.concat([product_info,pd.DataFrame(risk_return_data).T],axis=1)    

    #返回字典
    return {'stat_date':stat_date,'custom_benchmark':custom_benchmark,'product_data':product_data,'image':images,"image_dir":image_dir}